<?php
require_once 'common.php';
require_once 'OaAppModel.php';
require_once 'OaAppServerModel.php';

/**
 * 运营数据模型
 * @author wangjiajun
 *
 */
class OaOperatingDataModel extends OpModel
{
    private $_conn;
    private $_appModel;
    private $_appServerModel;
    
    public function __construct($logger = null)
    {
        if (is_null($logger)) {
            $logger = getOaModelLogger();
        }
        parent::__construct($logger);
        $dbConfig = Oa_Util_Config::getSingleDbConfig(OAII_OADW_DB_CLUSTER_ID);
        $_conn = oci_connect($dbConfig['user'], $dbConfig['pass'], 
        	"{$dbConfig['host']}:{$dbConfig['port']}/{$dbConfig['name']}", 'AL32UTF8');
        if (!$_conn) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $this->_conn = $_conn;
        $this->_appModel = new OaAppModel(OaAppModel::APP_CONFIG_ENV_PRODUCTION, $logger);
        $this->_appServerModel = new OaAppServerModel(OaAppModel::APP_CONFIG_ENV_PRODUCTION, $logger);
    }
    
    /**
     * 注册、登录数据查询，细分到分区
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $areaId null表示查询所有分区
     * @return array|boolean
     */
    public function getRegLoginDataByArea($fromDate, $toDate, $appId, $offset, $count, 
        $areaId = null)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBAUTHENLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') 
        		ORDER BY LOGDATE ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 注册、登录数据总数查询，细分到分区
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $areaId null表示查询所有分区
     * @return int|boolean
     */
    public function getRegLoginDataByAreaCount($fromDate, $toDate, $appId, $areaId = null)
    {
        $sql = "SELECT COUNT(*) FROM TBAUTHENLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 注册、登录数据查询，细分到应用
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getRegLoginDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBREGISTER WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 注册、登录数据总数查询，细分到应用
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getRegLoginDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBREGISTER WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 安全产品使用情况查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getEkeyDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBAUTHENEKEY WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY LOGDATE ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 安全产品使用情况总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getEkeyDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBAUTHENEKEY WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 一号多区情况查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getOneAccountMultiAreaDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBAUTHENEXLOG WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY LOGDATE ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 一号多区情况总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getOneAccountMultiAreaDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBAUTHENEXLOG WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 消耗情况查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $areaId null表示查询所有分区
     * @return array|boolean
     */
    public function getConsumeDataByArea($fromDate, $toDate, $appId, $offset, $count, 
        $areaId = null)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBOACONSUME WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') 
        		ORDER BY CREATEDATE ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        $areaIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
            $areaIds[] = $row['AREAID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        $areas = $this->_appServerModel->getAreaMulti($areaIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
            $areaId = $row['AREAID'];
            if (isset($areas[$areaId])) {
                $row['AREANAME'] = $areas[$areaId]['name'];
            } else {
                $row['AREANAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 消耗情况总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $areaId null表示查询所有分区
     * @return int|boolean
     */
    public function getConsumeDataByAreaCount($fromDate, $toDate, $appId, $areaId = null)
    {
        $sql = "SELECT COUNT(*) FROM TBOACONSUME WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 个体数据查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $areaId null表示查询所有分区
     * @return array|boolean
     */
    public function getPayDataByArea($fromDate, $toDate, $appId, $offset, $count, 
        $areaId = null)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBOAPAYLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') 
        		ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        $areaIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
            $areaIds[] = $row['AREAID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        $areas = $this->_appServerModel->getAreaMulti($areaIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
            $areaId = $row['AREAID'];
            if (isset($areas[$areaId])) {
                $row['AREANAME'] = $areas[$areaId]['name'];
            } else {
                $row['AREANAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 个体数据总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $areaId null表示查询所有分区
     * @return int|boolean
     */
    public function getPayDataByAreaCount($fromDate, $toDate, $appId, $areaId = null)
    {
        $sql = "SELECT COUNT(*) FROM TBOAPAYLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 实时收入查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $areaId null表示查询所有分区
     * @return array|boolean
     */
    public function getIncomeDataByArea($fromDate, $toDate, $appId, $offset, $count, 
        $areaId = null)
    {
        $sql = "SELECT * FROM (
        	SELECT T.*, ROWNUM RN FROM (
        		SELECT appid, a.areaid, logdate, a.itemid, nvl(itemname, '未知道具') itemname, 
        		buychannel, paytypeid, amount, itemnum, cnt1 accountnum, cnt2 personnum 
        		From tbitem b, (SELECT appid, areaid, trunc(logdate) logdate, itemid, buychannel, 
				paytypeid, sum(amount) amount, sum(itemnum) itemnum, COUNT(*) cnt1, 
				COUNT(distinct personnum) cnt2 From tbincomelog WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') 
        		GROUP BY appid, areaid, trunc(logdate), itemid, buychannel, paytypeid) a 
 				WHERE b.serviceid(+)=a.appid AND b.itemid(+)=a.itemid
        	) T WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $this->_logger->info($sql);
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        $areaIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
            $areaIds[] = $row['AREAID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        $areas = $this->_appServerModel->getAreaMulti($areaIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
                $row['PAYTYPENAME'] = $apps[$appId]['game_currency_name'];
            } else {
                $row['APPNAME'] = '';
                $row['PAYTYPENAME'] = '';
            }
            $areaId = $row['AREAID'];
            if (isset($areas[$areaId])) {
                $row['AREANAME'] = $areas[$areaId]['name'];
            } else {
                $row['AREANAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 实时收入总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $areaId null表示查询所有分区
     * @return int|boolean
     */
    public function getIncomeDataByAreaCount($fromDate, $toDate, $appId, $areaId = null)
    {
        $sql = "SELECT COUNT(*) FROM TBINCOMELOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 分红数据查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getBonusDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBOABONUS WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 分红数据总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getBonusDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBOABONUS WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 积分数据查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $areaId null表示查询所有分区
     * @return array|boolean
     */
    public function getPointsDataByArea($fromDate, $toDate, $appId, $offset, $count, 
        $areaId = null)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBCREDITLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') 
        		ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        $areaIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
            $areaIds[] = $row['AREAID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        $areas = $this->_appServerModel->getAreaMulti($areaIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
            $areaId = $row['AREAID'];
            if (isset($areas[$areaId])) {
                $row['AREANAME'] = $areas[$areaId]['name'];
            } else {
                $row['AREANAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 积分数据总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $areaId null表示查询所有分区
     * @return int|boolean
     */
    public function getPointsDataByAreaCount($fromDate, $toDate, $appId, $areaId = null)
    {
        $sql = "SELECT COUNT(*) FROM TBCREDITLOG WHERE APPID = :appId";
        if (!is_null($areaId)) {
            $sql .= " AND AREAID = :areaId";
        }
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!is_null($areaId)) {
            oci_bind_by_name($st, ':areaId', $areaId);
        }
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 统一帮助数据查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getHelpDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBHELPINFO WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 统一帮助数据总数查询
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getHelpDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBHELPINFO WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    /**
     * 高频问题前十
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @return array|boolean
     */
    public function getHelpQuestionDataByApp($fromDate, $toDate, $appId, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM TBHELPLOG WHERE APPID = :appId AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') 
        		AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd') ORDER BY CREATETIME ASC 
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $appIds = array();
        foreach ($rows as $row) {
            $appIds[] = $row['APPID'];
        }
        $apps = $this->_appModel->getAppMulti($appIds);
        foreach ($rows as &$row) {
            $row['LOGDATE'] = date('Y-m-d', strtotime($row['LOGDATE']));
            $appId = $row['APPID'];
            if (isset($apps[$appId])) {
                $row['APPNAME'] = $apps[$appId]['name'];
            } else {
                $row['APPNAME'] = '';
            }
        }
        unset($row);
        return $rows;
    }
    
    /**
     * 高频问题前十总数
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param int $appId
     * @return int|boolean
     */
    public function getHelpQuestionDataByAppCount($fromDate, $toDate, $appId)
    {
        $sql = "SELECT COUNT(*) FROM TBHELPLOG WHERE APPID = :appId";
        $sql .= " AND LOGDATE >= TO_DATE(:fromDate, 'yyyy-mm-dd') AND LOGDATE <= TO_DATE(:toDate, 'yyyy-mm-dd')";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':appId', $appId);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $row = oci_fetch_row($st);
        if ($row === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return (int) $row[0];
    }
    
    public function getDataByTable($table, $offset, $count)
    {
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (
        		SELECT * FROM $table
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        oci_bind_by_name($st, ':begin', $offset);
        $end = $offset+$count;
        oci_bind_by_name($st, ':end', $end);
        if (!oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
}
